<?php

class Track extends AppModel
{
    var $belongsTo = array("Post", "Artist");
    var $hasMany = array("Like", "Track_artist");
    
    function getPopularArtists()
    {
        $artists = $this->query("
            SELECT `artist`, 
            COUNT(`tracks`.`id`) as `count`
            FROM tracks 
            WHERE `artist` <> ''
            AND tracks.date > DATE_SUB(tracks.date, INTERVAL 7 DAY) 
            GROUP BY `artist`
            ORDER BY `count` DESC
            LIMIT 100");
        
        return $artists;
    }
    
    function getPopularDJs()
    {
        $artists = $this->query("
            SELECT `dj`, 
            COUNT(`tracks`.`id`) as `count`
            FROM tracks 
            WHERE `dj` <> ''
            AND tracks.date > DATE_SUB(tracks.date, INTERVAL 30 DAY) 
            GROUP BY `dj`
            ORDER BY `count` DESC
            LIMIT 100");
        
        return $artists;
    }
    
    function findByTag($tag, $order, $limit, $page)
    {
        $offset = $limit*($page-1);
        $artists = $this->query("
            SELECT `Artist`.`id`, `Artist`.`name`, `Post`.*, `Track`.*

            FROM `artists` AS `Artist` 
	        JOIN `object_tags` AS `Object_tag` ON `Object_tag`.`object_id` = `Artist`.`id`
            JOIN `tags` AS `Tag` ON `Tag`.`tag` = '$tag' AND `Object_tag`.`tag_id` = `Tag`.`id`
            JOIN `tracks` AS `Track` ON `Track`.`artist_id` = `Artist`.`id`
            JOIN `posts` AS `Post` ON `Track`.`post_id` = `Post`.`id`

            GROUP BY `Track`.`id`
            ORDER BY $order
            LIMIT $limit OFFSET $offset"); 
            
        return $artists;       
    }
}

?>